* Figure3prep.do  09/17/22
* Create children (0-17) records from annual CDC Wonder data (age x gender)
* use 2020 population for 2021, as CDC Wonder does
* the result is 22 years X 1 race X 2 genders X 2 ages X 9 Census divisions X 3 mcd codes = 2,376 observations
*     plus another 50% more observations if retaining the "All" ages series

use "PopSeriesAnnual.dta" if year==2020, clear
replace year = 2021
tempfile fi21
save `fi21', replace
use "PopSeriesAnnual.dta" if year<=2020, clear
append using `fi21'
sort gender age race state place year
save `fi21', replace

use "DeathSeriesAnnual.dta" if (race=="All") & (gender==`"{"Male"}"' | gender=="All") & (age=="0+" | age=="18+") & ucd=="Drug" & (mcd=="T402T403" | mcd=="T400T401T404" | mcd=="Opioid"), clear
sort gender age race state place year
merge gender age race state place year using `fi21'
drop if race!="All"  | age=="0-44" | age=="45-64"
tab _merge
drop _merge

* recalculate All gender records to be Female
replace gender=`"{"Female"}"' if gender=="All"
egen tmpsum = sum(pop), by(age mcdtitle state place year)
replace pop = 2*pop - tmpsum if gender==`"{"Female"}"'
drop tmpsum
egen tmpsum = sum(deaths), by(age mcdtitle state place year)
egen tmpnum = count(deaths), by(age mcdtitle state place year)
replace deaths = 2*deaths - tmpsum if gender==`"{"Female"}"'
replace deaths = . if tmpnum<2 & gender==`"{"Female"}"'   // missing a component of the sum
drop tmpsum tmpnum

* create children records
* *** Note that the derived 0-17 category will be inaccurate for the year 2021.  This is addressed in Figure3.do ***
gen byte adultsonly = age=="18+"
reshape groups adultsonly 0 1
reshape cons ucdtitle mcdtitle gender race state place year
reshape vars deaths pop
reshape wide
gen long deaths2 = deaths0 - deaths1
gen long pop2 = pop0 - pop1
reshape groups adultsonly 0 1 2
reshape long
gen str5 age = "All" if adultsonly==0
replace  age = "18+" if adultsonly==1
replace  age = "0-17" if adultsonly==2
drop adultsonly
sort ucdtitle mcdtitle gender age race state place year
